﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace Common.Extension
{
    /// <summary>
    /// 数据库参数扩展
    /// </summary>
    public static class DbParameterExtension
    {
        /// <summary>
        /// 转换为MSSQL的DML参数声明语句
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string ToMSDML(this DbParameter[] parameters)
        {
            if (parameters == null || !parameters.Any())
            {
                return null;
            }
            StringBuilder sb = new StringBuilder();
            foreach (var item in parameters)
            {
                sb.AppendLine($"declare @{item.ParameterName} {TranslateDbType(item)}; set @{item.ParameterName}={QuotaValue(item)}");
            }
            return sb.ToString();
        }
        /// <summary>
        /// 转换为MYSQL的DML参数声明语句
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string ToMYDML(this DbParameter[] parameters)
        {
            if (parameters.IsNullOrEmpty())
            {
                return null;
            }
            StringBuilder sb = new StringBuilder();
            foreach (var item in parameters)
            {
                sb.AppendLine($"set @{item.ParameterName}={QuotaValue(item)};");
            }
            return sb.ToString();
        }
        private static string QuotaValue(DbParameter para)
        {
            DbType[] types = new DbType[] { DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String, DbType.StringFixedLength,DbType.Time,DbType.Date,
                DbType.DateTime, DbType.DateTime2, DbType.Guid, DbType.Object, DbType.Xml
            };
            return types.Contains(para.DbType) ? $"'{para.Value}'" : para.Value.ToString();
        }
        private static string TranslateDbType(DbParameter para)
        {
            string name = para.DbType.ToString().ToLower();
            switch (para.DbType)
            {
                case DbType.AnsiString: name = $"char({para.Size})"; break;
                case DbType.AnsiStringFixedLength: name = $"varchar({para.Size})"; break;
                case DbType.Boolean: name = "bit"; break;
                case DbType.SByte:
                case DbType.Byte: name = "tinyint"; break;
                case DbType.Currency: name = "money"; break;
                case DbType.Date: name = "date"; break;
                case DbType.Time:
                case DbType.DateTime: name = "datetime"; break;
                case DbType.DateTime2: name = "datetime2"; break;
                case DbType.DateTimeOffset: name = "datetimeoffset"; break;
                case DbType.Decimal: name = "decimal"; break;
                case DbType.Double: name = "float"; break;
                case DbType.Binary: name = "binary"; break;
                case DbType.Guid: name = "UniqueIdentifier"; break;
                case DbType.Single:
                case DbType.UInt16:
                case DbType.Int16: name = "smallint"; break;
                case DbType.UInt32:
                case DbType.Int32: name = "int"; break;
                case DbType.UInt64:
                case DbType.Int64: name = "bigint"; break;
                case DbType.Object: name = "Variant"; break;
                case DbType.String: name = $"nvarchar({para.Size})"; break;
                case DbType.StringFixedLength: name = $"nchar({para.Size})"; break;
                case DbType.VarNumeric: name = "numeric"; break;
                case DbType.Xml: name = "xml"; break;
            }
            return name;
        }
    }
}
